package com.testmanager.testmananger.pagetest;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.awt.print.Book;
import java.util.List;

@Service
@Slf4j
@Transactional

public class BookQueryService {

    //@Autowired
    @PersistenceContext
    EntityManager entityManager;
    /**
     * select type,max(price) maxPrice,sum(price) sumPrice from books group by type
     */
    public Page<BookInfo> groupBy(int index, int pageSize){
        //新建一个页面，存放页面信息

        Pageable page = new PageRequest(index, pageSize);
        //criteriaBuilder用于构建CriteriaQuery的构建器对象
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
        CriteriaQuery<BookInfo> criteriaQuery = criteriaBuilder.createQuery(BookInfo.class);
        //获取查询实例的属性，select * from books
        Root<Book> root = criteriaQuery.from(Book.class);
        //相当于select type,max(price) maxPrice,sum(price) sumPrice from books中select 与 from之间的部分
        criteriaQuery.multiselect(root.get("type"), criteriaBuilder.max(root.get("price")), criteriaBuilder.sum(root.get("price")));
        //where type = 1
        criteriaQuery.where(criteriaBuilder.equal(root.get("type"), 1));
        //group by type
        criteriaQuery.groupBy(root.get("type"));
        //criteriaQuery拼成的sql是select type,max(price) maxPrice,sum(price) sumPrice from books group by type；查询出的列与对象BookInfo的属性对应
        //记录当前sql查询结果总条数
        List<BookInfo> counts = entityManager.createQuery(criteriaQuery).getResultList();
        //sql查询对象
        TypedQuery<BookInfo> createQuery = entityManager.createQuery(criteriaQuery);
        //设置分页参数
        createQuery.setFirstResult(index*pageSize);
        createQuery.setMaxResults(pageSize);
        //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
        return new PageImpl<BookInfo>(createQuery.getResultList(), page, counts.size());
    }

}
